using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;
using ObjectMap;


namespace BusinessLayer
{
    public class Export
    {
        private string _PathAccess = string.Empty;

        public string PathAccess
        {
            get { return _PathAccess; }
            set { _PathAccess = value; }
        }

        public Export()
        {
 
        }

        public void ExportREF_JABATAN_A1(DataTable dt)
        {
            List<REF_JABATAN_A1Map> oList = new List<REF_JABATAN_A1Map>();
            oList = GetLstRefJabatan(dt);

            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _PathAccess + ";Jet OLEDB:Database Password=e_spt_21_2009;";
            string strInsertIN = string.Empty;
            string strInsertOut = string.Empty;
            string deleteIn = string.Empty;
            string deleteOut = string.Empty;

            if (Tools.IsHasData("", "REF_JABATAN_A1", _PathAccess))
            {
                /* hapus data master jabatan sebelumnya */
                deleteIn = "delete from REF_JABATAN_A1";
                OleDbConnection con = new OleDbConnection();
                con.ConnectionString = connectionString;
                con.Open();
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandText = deleteIn;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            else
            {
                if (Tools.IsHasData("", "REF_JABATAN_A1", _PathAccess))
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        deleteIn = "delete from REF_JABATAN_A1 where ID_JAB = '" + Convert.ToDouble(dr[0]) + "'";
                        OleDbConnection con = new OleDbConnection();
                        con.ConnectionString = connectionString;
                        con.Open();
                        OleDbCommand cmd = con.CreateCommand();
                        cmd.CommandText = deleteIn;
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        con.Close();
                    }
                }
            }

            deleteIn = "delete from REF_JABATAN_A1";
            OleDbConnection con2 = new OleDbConnection();
            con2.ConnectionString = connectionString;
            con2.Open();
            OleDbCommand cmd2 = con2.CreateCommand();
            cmd2.CommandText = deleteIn;
            cmd2.CommandType = CommandType.Text;
            cmd2.ExecuteNonQuery();
            cmd2.Dispose();
            con2.Close();

            foreach (REF_JABATAN_A1Map oMap in oList)
            {
                strInsertIN = "insert into REF_JABATAN_A1(ID_JAB,NM_JAB)" +
                            "values(" + "'" + oMap.ID_JAB.ToString() + "','" + oMap.NM_JAB.ToString().Replace("'", " ") + "'" + ")";

                OleDbConnection con = new OleDbConnection();
                con.ConnectionString = connectionString;
                con.Open();
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandText = strInsertIN.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
        }

        private List<REF_JABATAN_A1Map> GetLstRefJabatan(DataTable dt)
        {
            List<REF_JABATAN_A1Map> oList = new List<REF_JABATAN_A1Map>();
            Dictionary<string, int> dictionary = new Dictionary<string, int>();

            foreach (DataRow dr in dt.Rows)
            {
                if (dr[0].ToString().Trim() != string.Empty)
                {
                    if (!dictionary.ContainsKey(dr[0].ToString()))
                    {
                        dictionary.Add(dr[0].ToString(), 0);
                        REF_JABATAN_A1Map oMap = new REF_JABATAN_A1Map();
                        oMap.ID_JAB = dr[0].ToString();
                        oMap.NM_JAB = dr[1].ToString();
                        oList.Add(oMap);
                    }
                }
            }

            return oList;
        }

        private List<Data1721A1> GetLstData1721A1(List<Data1721A1> oListParam)
        {
            Dictionary<string, int> dict = new Dictionary<string, int>();
            List<Data1721A1> oList = new List<Data1721A1>();
          

            foreach (Data1721A1 oMap in oListParam)
            {
                if (!oMap.NpwpPegawai.Trim().Equals(string.Empty))
                {
                    if (!dict.ContainsKey(oMap.NpwpPegawai.Trim() + oMap.BranchCode))
                    {
                        dict.Add(oMap.NpwpPegawai.Trim() + oMap.BranchCode, 0);
                        oList.Add(oMap);
                    }
                }
            }

            return oList;
        }

        public void Export1721_A1(List<Data1721A1> oListParam, List<DS_Form_Induk> lstFormInduk, string branchCode, string mainNPWP)
        {
            string strInsertIN = string.Empty;
            string strInsertOut = string.Empty;
            string deleteIn = string.Empty;
            string deleteOut = string.Empty;
            string tblJabatan = "DAT_1721A1";

            List<Data1721A1> olist = new List<Data1721A1>();
            olist = GetLstData1721A1(oListParam);
            olist.Sort();

            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathAccess + ";Jet OLEDB:Database Password=e_spt_21_2009;";
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = connectionString;

            if (Tools.IsHasData("", tblJabatan, PathAccess))
            {
                /* delete all */
                deleteIn = "delete from DAT_1721A1";
                con.Open();
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandText = deleteIn;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }


            foreach (Data1721A1 d in olist)
            {
                try
                {
                    strInsertIN = "insert into DAT_1721A1(KD_FORM,NO_URUT,NPWP_PERSH,TH_PAJAK,KD_PEMBETULAN_KE,NPWP_PEG,NAMA_PEG,ALM_PEG," +
                                  " JAB_PEG,JNS_KEL,FLG_ASING,STS_KWN,STS_KEL,JML_TGN,MASA_1,MASA_2,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12," +
                                  " A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A22a,A22b,A23,A24,FLG_STS,FLG_23,FLG_24,MS_THN_POT,MS_THN_HITUNG,UMRBLN,UMRTHN,FLG_TNJ_PPH,FLG_PTKP,FLG_PEG)" +
                                "values(" + "'" + d.KdForm + "'," + "'" + d.NoUrut + "'," + "'" + d.NpwpPerusahaan + "'," + "'" + d.TahunPajak + "'," + "'" + d.KdPembentulanKe +
                                "'," + "'" + d.NpwpPegawai + "'," + "'" + Tools.RemovePetik(d.NamaPegawai) + "'," + "'" + Tools.RemovePetik(d.AlamatPegawai) +
                                "'," + "'" + d.JabatanPegawai + "'," + "'" + d.JenisKelamin + "'," + "'" + d.FlagAsing +
                                "'," + "'" + d.StatusKawin + "'," + "'" + d.StatusKeluarga + "'," + "'" + d.JumlahTanggungan +
                                "'," + "'" + d.Masa1 + "'," + "'" + d.Masa2 + "'," + "'" + d.Angka1 +
                                "'," + "'" + d.Angka2 + "'," + "'" + d.Angka3 + "'," + "'" + d.Angka4 +
                                "'," + "'" + d.Angka5 + "'," + "'" + d.Angka6 + "'," + "'" + d.Angka7 +
                                "'," + "'" + d.Angka8 + "'," + "'" + d.Angka9 + "'," + "'" + d.Angka10 +
                                "'," + "'" + d.Angka11 + "'," + "'" + d.Angka12 + "'," + "'" + d.Angka13 +
                                "'," + "'" + d.Angka14 + "'," + "'" + d.Angka15 + "'," + "'" + d.Angka16 +
                                "'," + "'" + d.Angka17 + "'," + "'" + d.Angka18 + "'," + "'" + d.Angka19 +
                                "'," + "'" + d.Angka20 + "'," + "'" + d.Angka21 + "'," + "'" + d.Angka22 +
                                "'," + "'" + d.Angka22A + "'," + "'" + d.Angka22B + "'," + "'" + d.Angka23 +
                                "'," + "'" + d.Angka24 + "'," + "'" + d.FlagStatus + "'," + "'" + d.Flag23 + "'" + ",'" + d.Flag24 +
                                "'," + "'" + d.MasaTahungPotong + "'," + "'" + d.MasaTahungHitung + "'," + "'" + d.UmrBulan +
                                "'," + "'" + d.UmrTahun + "'," + "'" + d.FlagTunjanganPPH + "'," + "'" + d.FlagPTKP +
                                "'," + "'" + d.FlagPegawai + "'" +
                                ")";

                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = strInsertIN.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();
                }
                catch (Exception e)
                {
                    e.ToString();
                    con.Close();
                }
            }

            deleteIn = string.Empty;
            strInsertIN = string.Empty;

            if (Tools.IsHasData("", "DS_FORM_INDUK", PathAccess))
            {
                int msPajak = 0;
                int thPajak = 0;
                string kdForm = string.Empty;

                foreach (DS_Form_Induk s in lstFormInduk)
                {
                    msPajak = s.MasaPajak;
                    thPajak = s.Th_Pajak;
                    kdForm = s.Kd_Form.Trim();

                    deleteIn = "delete from DS_FORM_INDUK where KD_FORM = '" + kdForm + "'" +
                               " and MS_PAJAK = " + msPajak + "" + " and TH_PAJAK = " + thPajak + "" + "";

                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = deleteIn;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();
                }

            }

            foreach (DS_Form_Induk ss in lstFormInduk)
            {
                strInsertIN = "insert into DS_FORM_INDUK(NPWP,KD_FORM,JENIS_DOKUMEN,MS_PAJAK,TH_PAJAK,KD_PEMBETULAN_KE,NOBUKTI,KD_JENIS_PAJAK,DAT_FORM,TGL_REKAM,TGL_UBAH,NIP)" +
                         "values('" + ss.NPWP + "','" + ss.Kd_Form + "','" + ss.Jenis_Dokumen + "','" +
                         ss.MasaPajak + "','" + ss.Th_Pajak + "','" + ss.KD_PEMBETULAN_KE + "','" + ss.NOBUKTI + "','" + ss.KD_JENIS_PAJAK + "','" +
                         ss.DAT_FORM + "','" + ss.TGL_REKAM + "','" + ss.TGL_UBAH + "','" + ss.NIP + "'" + ")";

                OleDbConnection conInsert = new OleDbConnection();
                conInsert.ConnectionString = connectionString;
                conInsert.Open();
                OleDbCommand cmdInsert = conInsert.CreateCommand();
                cmdInsert.CommandText = strInsertIN.ToString();
                cmdInsert.CommandType = CommandType.Text;
                cmdInsert.ExecuteNonQuery();
                cmdInsert.Dispose();
                conInsert.Close();
            }

            deleteIn = string.Empty;
            strInsertIN = string.Empty;

            if (Tools.IsHasData("", "DAT_PEG_1721A1", PathAccess))
            {
                /* delete all */
                deleteIn = "delete from DAT_PEG_1721A1";
                con.Open();
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandText = deleteIn;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }

            foreach (Data1721A1 d in olist)
            {
                strInsertIN = "insert into DAT_PEG_1721A1(NPWP_PEG,NAMA_PEG,ALAMAT_PEG,JAB_PEG,JNS_KLM_PEG,STS_PEG,JML_TG_PEG,FLAG_ASING,STS_KWN,FLAG_PEG)" +
                            "values('" + d.NpwpPegawai + "','" + Tools.RemovePetik(d.NamaPegawai) + "','" + Tools.RemovePetik(d.AlamatPegawai) + "','" + Tools.RemovePetik(d.JabatanPegawai) + "','" +
                                    d.JenisKelamin + "','" + d.StatusKawin + "','" + d.JumlahTanggungan + "','" + d.FlagAsing + "','" + d.StatusKawin + "','" + d.FlagStatus + "')";

                con.Open();
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandText = strInsertIN.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }

        }
        
    }
}
